﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Net.Mail;
using System.Text;
using CRMFramework;
using Newtonsoft.Json;
using System.Web;
using System.IO;
using System.Net;
using System.Configuration;

namespace CRMBiz
{
    public static class ClientProfileHandler
    {
        /// <summary>
        /// function for get selected data
        /// </summary>
        /// <param name="key"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public static DataTable BindDataTableForApplication(int appid, string OperatorID)
        {
            string sql = string.Empty;
            DataTable dt = null;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@app_id", appid),
                    DBhelper.SetParameter("@OperatorID", OperatorID),
                    DBhelper.SetParameter("@LatestTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")),
                };
                var strSql = new StringBuilder("INSERT INTO crm_Recent(app_id,OperatorID,LatestTime) values(@app_id,@OperatorID,@LatestTime);")
                    .Append(" SELECT crm_application.*,udf_getformatedDate(crm_application.CreatedDate) AS cdate,crm_contact.`ContactID`,`crm_contact`.`FirstName`,`crm_contact`.`SurName`,`crm_contact`.`MiddleName`,crm_contact.Mobile,crm_contact.Email")
                    .Append(" FROM crm_application LEFT JOIN `crm_applicationcontact` ON `crm_applicationcontact`.`app_id`=`crm_application`.`app_id` ")
                    .Append(" LEFT JOIN  `crm_contact` ON  crm_contact.`ContactID`=`crm_applicationcontact`.`ContactID` WHERE `crm_application`.`app_id`=@app_id;");

                dt = DBhelper.ExecuteDataset(strSql.ToString(), para).Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }

        /// <summary>
        /// function for get folder:
        /// </summary>
        /// <param name="key"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public static string GetFolderName(int appid)
        {
            string folderName = string.Empty;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@p_app_id", appid),
                };
                folderName = DBhelper.ExecuteScalar("Select Folder from crm_application where app_id=@p_app_id and deletedFlag=0", para);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return folderName;
        }



        /// <summary>
        /// function to bind last result
        /// </summary>
        /// <param name="key"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public static DataTable BindLastResultData()
        {
            DataTable dt;
            try
            {
                dt = DBhelper.ExecuteDataset("SELECT * FROM crm_lastresult ORDER BY OrderNo").Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }

        /// <summary>
        /// function to bind awaited docs
        /// </summary>
        /// <param name="key"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public static DataTable BindAwaitedDocs()
        {

            try
            {
                return DBhelper.ExecuteDataset("SELECT AwaitedDocs  FROM crm_awaitdocs").Tables[0];
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }



        private static string getOnlyFileName(string fullPath)
        {
            fullPath = fullPath.Replace('\\', ';');
            string[] arr = fullPath.Split(';');
            string filename = arr[arr.Length - 1];
            return filename;

        }

        /// <summary>
        /// function to send email
        /// </summary>
        /// <param name="key"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public static string SendEmail(string fromName, string smtpServer, string sendingName, string password, string toName, string attachmentFile, string attachmentsDrag, string subject, string bodyContent)
        {
            try
            {
                MailMessage mail = new MailMessage();
                mail.To.Add(toName);
                var obj = fromName;
                mail.From = new MailAddress(obj.ToString(), sendingName);
                mail.Subject = subject;
                mail.Body = bodyContent;
                mail.IsBodyHtml = true;
                string attachmentList = string.Empty;

                if (attachmentFile != "")
                {
                    string folderPath = System.Web.HttpContext.Current.Server.MapPath("~/uploads");
                    attachmentFile = folderPath + "\\" + attachmentFile;
                    string dirName = Path.GetDirectoryName(attachmentFile);
                    if (attachmentFile != "")
                    {
                        string[] fileEntries = Directory.GetFiles(dirName);
                        foreach (string fileName in fileEntries)
                        {
                            mail.Attachments.Add(new Attachment(fileName));
                            attachmentList += getOnlyFileName(fileName) + " ,";
                        }
                    }
                }
                else if (attachmentFile == "")
                {
                    attachmentFile = "";
                }
                if (attachmentsDrag != "")
                {
                    attachmentsDrag = HttpContext.Current.Server.MapPath(attachmentsDrag);
                    string dirNameDg = Path.GetDirectoryName(attachmentsDrag);
                    if (attachmentsDrag != "")
                    {
                        string[] findfiles = Directory.GetFiles(dirNameDg);
                        foreach (string dragFileName in findfiles)
                        {
                            mail.Attachments.Add(new Attachment(dragFileName));
                            attachmentList += getOnlyFileName(dragFileName) + " ,";

                        }
                    }
                }
                else
                {
                    attachmentsDrag = "";
                }

                SmtpClient smtp = new SmtpClient();
                smtp.Host = smtpServer;
                smtp.Port = 587;
                smtp.EnableSsl = true;
                smtp.UseDefaultCredentials = true;
                smtp.Timeout = 2000000;
                smtp.Credentials = new System.Net.NetworkCredential(fromName, password);
                smtp.EnableSsl = true;
                smtp.Send(mail);
                mail.Dispose();

                if (attachmentFile != "")
                {
                    string dirNamea = Path.GetDirectoryName(attachmentFile);
                    string[] fileEntriess = Directory.GetFiles(dirNamea);
                    foreach (string doc in fileEntriess)
                    {
                        File.Delete(doc);
                    }
                }

                if (attachmentsDrag != "")
                {
                    string dirNamea = Path.GetDirectoryName(attachmentsDrag);
                    string[] fileEntriess = Directory.GetFiles(dirNamea);
                    foreach (string doc in fileEntriess)
                    {
                        File.Delete(doc);
                    }
                }

                // Prepare Log text to write 

                return attachmentList;
            }
            catch (Exception ex)
            {
                return "0";
                throw ex;
            }

        }



        /// <summary>
        /// function to get all contact list
        /// </summary>
        /// <param name="key"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public static DataTable GetApplicantList(int app_id, int contact_id)
        {
            DataTable dt = null;

            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@app_id", app_id),
                    DBhelper.SetParameter("@ContactID", contact_id),
                };

                var strSql = new StringBuilder("SELECT FirstName, MiddleName, SurName, Title, Mobile, Email, HomePhone, State,CAST(DATE_FORMAT(DateBirth,'%d %b %Y') as Char)  AS DateBirth, udf_getformatedDate(crm_contact.CreateDate) AS CreateDate, crm_contact.ContactID ");

                if (app_id < 0 && contact_id > 0)
                    strSql.Append(" FROM crm_contact WHERE ContactID=@ContactID ORDER By CreateDate Desc");
                else if (app_id > 0)
                    strSql.Append(" FROM crm_contact INNER JOIN crm_applicationcontact ON crm_contact.ContactID = crm_applicationcontact.ContactID INNER JOIN crm_application ON crm_applicationcontact.app_id=crm_application.app_id WHERE crm_application.app_id=@app_id and crm_applicationcontact.deletedFlag=0 and crm_application.deletedFlag=0 ORDER By crm_applicationcontact.NoInApplication");
                dt = DBhelper.ExecuteDataset(strSql.ToString(), para).Tables[0];
            }
            catch (Exception)
            {
                throw;
            }
            return dt;
        }


        /// <summary>
        /// function to bind app-forms list
        /// </summary>
        /// <param name="key"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public static DataTable BindAppForms(int app_id)
        {
            DataTable dt = null;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@p_app_id", app_id),
                };

                var strSql = new StringBuilder("SELECT  CONCAT(c.FirstName,' ',c.MiddleName,' ',c.SurName) AS ClientName,udf_getformatedDate(a.CreatedDate) AS cdate,af.AppFormId AS FormID ,a.Assets ,a.CreatedDate AS Age ,a.DesiredAmount,a.PurposeSummary AS LoanPurpose ,IFNULL(a.comesfrom,'') AS comesfrom ")
                    .Append(" FROM  crm_application a  INNER JOIN  (SELECT CASE WHEN CombineApp>0 THEN CombineApp ELSE app_id END AS appid , ContactID FROM `crm_applicationcontact`  WHERE app_id=@p_app_id and deletedFlag=0 ) ac ")
                    .Append(" ON a.app_id=ac.appid INNER JOIN crm_applicationappform af ON af.ApplicationId=ac.appid INNER JOIN crm_contact c ON c.ContactID=ac.ContactID");

                dt = DBhelper.ExecuteDataset(strSql.ToString(), para).Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }

        /// <summary>
        /// function to remove duplicate App being used from Application Profile
        /// </summary>
        /// <param name="key"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public static bool RemoveDuplicateData(int applicationId, int appformId)
        {
            bool flag = false;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@ApplicationId", applicationId),
                    DBhelper.SetParameter("@AppFormId", appformId), 
                };

                StringBuilder sql = new StringBuilder();
                DataTable dt = new DataTable();
                sql = new StringBuilder("select COUNT( * ) AS total from crm_application where app_id='" + appformId + "'");
                dt = DBhelper.ExecuteDataset(sql.ToString()).Tables[0];
                if (Convert.ToInt32(dt.Rows[0]["total"]) != 0)
                {
                    DataTable dts = new DataTable();
                    string selSql = "select app_info.app_id FROM app_info INNER JOIN crm_applicationappform ON  crm_applicationappform.AppFormId = app_info.app_id WHERE crm_applicationappform.ApplicationId='" + applicationId + "' limit 1";
                    dts = DBhelper.ExecuteDataset(selSql.ToString()).Tables[0];
                    string sbSql_ac = "insert into crm_applicationappform values(0,'" + appformId + "','" + dts.Rows[0]["app_id"] + "')";
                    DBhelper.ExecuteNonQuery(sbSql_ac, para);
                    flag = true;
                    return flag;
                }
                else if (Convert.ToInt32(dt.Rows[0]["total"]) == 0)
                {
                    Exception ex = new Exception();
                    throw ex;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return flag;
        }


        /// <summary>
        /// function to bind outsourceLender
        /// </summary>
        /// <param name="key"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public static DataTable BindOutSourceLender()
        {
            DataTable dt;
            try
            {
                dt = DBhelper.ExecuteDataset("SELECT * FROM crm_outsourcelender").Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }

        /// <summary>
        /// function to bind Referrer
        /// </summary>
        /// <param name="key"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public static DataTable BindReferrerData()
        {
            DataTable dt;
            try
            {
                dt = DBhelper.ExecuteDataset("SELECT * FROM crm_referrer").Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }

        /// <summary>
        /// function to show applicants first and last name
        /// </summary>
        /// <param name="key"></param>
        /// <param name="value"></param>
        /// <returns></returns>

        public static DataTable GetApplicantNames(int app_id)
        {
            DataTable dt = new DataTable();
            try
            {
                var sql = new StringBuilder("SELECT CONCAT(CONCAT(UCASE(LEFT(crm_contact.FirstName,1)),SUBSTRING(crm_contact.FirstName,2)),' ', CONCAT(UCASE(LEFT(crm_contact.MiddleName,1)),SUBSTRING(crm_contact.MiddleName,2)),' ',UCASE(crm_contact.SurName)) as FullName,crm_contact.ContactID")
                .Append(" FROM crm_contact INNER JOIN crm_applicationcontact ac ON crm_contact.ContactID = ac.ContactID WHERE ac.deletedFlag=0 AND ac.app_id=")
                .Append(app_id).Append(" LIMIT 3");
                dt = DBhelper.ExecuteDataset(sql.ToString()).Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }

    }
}
